﻿Imports System.Data.OleDb
Imports System.Net.Mail
Partial Public Class Details
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            'Repeater1.DataSource = SqlDataSource2
            'Repeater1.DataBind()

            'page count
            Dim id = Request.QueryString("ActionID")
            Dim Conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
            Dim myUpdateQuery As String = " UPDATE MasterTable SET [Count_page]  =  [Count_page]  +  1 WHERE ActionID = " & id
            Dim UpdateCount As New OleDbCommand(myUpdateQuery)
            UpdateCount.Connection = Conn

            Dim sqlCount As String = " SELECT [Count_page] FROM MasterTable WHERE ActionID = " & id
            Dim findIDCmd As New OleDbCommand(sqlCount, Conn)
            Conn.Open()
            Dim dbread As OleDbDataReader = findIDCmd.ExecuteReader
            dbread.Read()
            Dim Count As Integer = dbread("Count_page")
            Count += 1

            UpdateCount.ExecuteNonQuery()
            UpdateCount.Connection.Close()

            lblCount.Text = Count

            'binding data
            Dim sqlString As String = "select Title,Description,Image from MasterTable where ActionID=" + id + ""
            Dim dbcmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlString, Conn)
            Conn.Open()
            Dim reader As OleDb.OleDbDataReader = dbcmd.ExecuteReader()
            While reader.Read()
                lbltitle.Text = reader.Item(0).ToString()
                lbldescription.Text = reader.Item(1).ToString()
                image.ImageUrl = reader.Item(2).ToString()
            End While

            reader.Close()
            dbcmd.Dispose()


            ''display comments
            'Dim comment As String = "SELECT * FROM postings where [actionid]=" & id
            'Dim postingDBcmd As New OleDb.OleDbCommand(comment, Conn)
            'Dim db_read = postingDBcmd.ExecuteReader()
            'Repeater1.DataSource = db_read
            'Repeater1.DataBind()

            'display the rating on page load if user has already rated the article
            Dim select_string As String = "select score from ratings1 where Useridentity = '" + User.Identity.Name + "' and actionid_FK = " & id
            Dim select_command As OleDb.OleDbCommand = New OleDb.OleDbCommand(select_string, Conn)
            Dim rd As OleDb.OleDbDataReader = select_command.ExecuteReader()
            If rd.HasRows Then
                While rd.Read()
                    Rating1.CurrentRating = rd.Item(0).ToString()
                End While

            End If


            Dim select_str As String = "select score, Useridentity from ratings1 where actionid_FK = " & id
            Dim select_cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(select_str, Conn)
            'Conn.Open()
            Dim rdr As OleDb.OleDbDataReader = select_cmd.ExecuteReader()
            If rdr.HasRows Then

                Dim avgRating_string As String = "select sum(score), COUNT(Useridentity)  from ratings1 where actionid_FK = " & id
                Dim avgRating_command As OleDb.OleDbCommand = New OleDb.OleDbCommand(avgRating_string, Conn)
                Dim avg_reader As OleDb.OleDbDataReader = avgRating_command.ExecuteReader()
                If avg_reader.HasRows Then
                    While avg_reader.Read()
                        'displays number of users rated for that article
                        lblRating_users.Text = avg_reader.Item(1).ToString()
                        Dim score As Decimal = avg_reader.Item(0).ToString()
                        Dim users As Decimal = avg_reader.Item(1).ToString()
                        Dim avg As Decimal = score / users
                        'rounding off the score
                        If avg <= 0.49 Then
                            lblRating_avg.Text = 0
                            Avg_Rating.CurrentRating = 0
                        ElseIf avg >= 0.5 And avg <= 1.49 Then
                            lblRating_avg.Text = 1
                            Avg_Rating.CurrentRating = 1
                        ElseIf avg >= 1.5 And avg <= 2.49 Then
                            lblRating_avg.Text = 2
                            Avg_Rating.CurrentRating = 2
                        ElseIf avg >= 2.5 And avg <= 3.49 Then
                            lblRating_avg.Text = 3
                            Avg_Rating.CurrentRating = 3
                        ElseIf avg >= 3.5 And avg <= 4.49 Then
                            lblRating_avg.Text = 4
                            Avg_Rating.CurrentRating = 4
                        ElseIf avg >= 4.5 And avg <= 5 Then
                            lblRating_avg.Text = 5
                            Avg_Rating.CurrentRating = 5
                        End If

                    End While

                End If
            End If


        End If



    End Sub

    Protected Sub btnComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnComment.Click
        Dim id = Request.QueryString("ActionID")
        Dim Conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
        Conn.Open()
        Dim sql As String = "INSERT INTO Postings (author,title,comments,actionID) VALUES(@author,@title,@comments,@actionID)"
        Dim cmd As New OleDbCommand(sql, Conn)
        cmd.Parameters.AddWithValue("@author", User.Identity.Name)
        cmd.Parameters.AddWithValue("@title", tb_title.Text)
        cmd.Parameters.AddWithValue("@comments", tb_comment.Text)
        cmd.Parameters.AddWithValue("@actionID", id)
        cmd.ExecuteNonQuery()
        Conn.Close()

        Repeater1.DataBind()

        tb_comment.Text = ""
        tb_title.Text = ""

    End Sub

    Protected Sub btnRating_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnRating.Click
        'MsgBox("You Rated ", Rating1.CurrentRating)
        Dim id = Request.QueryString("ActionID")
        Dim Conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
        'To check whether user has already rated or not for the article
        Dim select_string As String = "select score from ratings1 where Useridentity = '" + User.Identity.Name + "' and actionid_FK = " & id
        Dim select_command As OleDb.OleDbCommand = New OleDb.OleDbCommand(select_string, Conn)
        Conn.Open()
        Dim reader As OleDb.OleDbDataReader = select_command.ExecuteReader()
        If reader.HasRows Then

            Dim update_string As String = "update ratings1 set score = '" & Rating1.CurrentRating() & "' where Useridentity = '" & User.Identity.Name & "' and actionid_FK = " & id
            Dim cmd As New OleDbCommand(update_string, Conn)
            cmd.ExecuteNonQuery()
            Conn.Close()

        Else

            Dim insert_string As String = "INSERT INTO ratings1 (Useridentity,score,actionid_FK) VALUES(@Useridentity,@score,@actionid_FK)"
            Dim insert_cmd As New OleDbCommand(insert_string, Conn)
            insert_cmd.Parameters.AddWithValue("@Useridentity", User.Identity.Name)
            insert_cmd.Parameters.AddWithValue("@score", Rating1.CurrentRating())
            insert_cmd.Parameters.AddWithValue("@actionid_FK", id)
            insert_cmd.ExecuteNonQuery()
            Conn.Close()
        End If
        
    End Sub

    Protected Sub btnTags_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnTags.Click
        Dim id = Request.QueryString("ActionID")
        Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())

        conn.Open()
        For Each item As ListItem In chkTags.Items
            If item.Selected Then
                Dim sql As String = "INSERT INTO TagContent (ActionID_FK,TagID_FK) VALUES(@ActionID_FK,@TagID_FK)"
                Dim cmd As New OleDbCommand(sql, conn)
                cmd.Parameters.AddWithValue("@ActionID_FK", id)
                cmd.Parameters.AddWithValue("@TagID_FK", item.Value)
                cmd.ExecuteNonQuery()
            End If

        Next

        Dim stringArray() As String = txtNewTag.Text.Split(" ")
        For Each mystring As String In stringArray
            If mystring <> "" Then
                Dim UPPERstring = mystring.ToUpper()
                Dim NewTagsSql As String = "INSERT INTO Tags (TagName) VALUES (@TagName)"
                Dim NewTagCmd As New OleDbCommand(NewTagsSql, conn)
                NewTagCmd.Parameters.AddWithValue("@TagName", UPPERstring)
                NewTagCmd.ExecuteNonQuery()
                lblTagsuccess.Visible = True
                txtNewTag.Text = ""

                Dim NewId As String = "SELECT TOP 1 TagID From Tags ORDER BY TagID Desc"
                Dim NewIdCmd As New OleDbCommand(NewId, conn)
                Dim dbread As OleDbDataReader = NewIdCmd.ExecuteReader
                dbread.Read()
                Dim IDOfNewTag = dbread("TagID")

                Dim tagJunctionSql As String = "INSERT INTO TagContent (ActionID_FK,TagID_FK) VALUES(@ActionID_FK,@TagID_FK)"
                Dim tagJunctionCmd As New OleDbCommand(tagJunctionSql, conn)
                tagJunctionCmd.Parameters.AddWithValue("@ActionID_FK", id)
                tagJunctionCmd.Parameters.AddWithValue("@TagID_FK", IDOfNewTag)
                tagJunctionCmd.ExecuteNonQuery()

            End If
        Next
        conn.Close()

    End Sub

    Public Function GetCurrentPageName() As String
        Dim spath As String = System.Web.HttpContext.Current.Request.Url.OriginalString
        Dim sRet As String = spath
        Return sRet
    End Function

    Protected Sub BtnEmail_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnEmail.Click

        If txtemail.Text = "" Then
            lblemailerror.Visible = True
            lblEmail.Visible = False

        Else
            Try
                Dim SmtpServer As New SmtpClient()
                Dim mail As New MailMessage()
                SmtpServer.Credentials = New Net.NetworkCredential("webmaster.leadherts@gmail.com", "plexpasslead")
                SmtpServer.Port = 587
                SmtpServer.Host = "smtp.gmail.com"
                mail.IsBodyHtml = True
                mail = New MailMessage()
                mail.From = New MailAddress("lead.herts@gmail.com")
                mail.To.Add(txtemail.Text)
                mail.Subject = User.Identity.Name.ToUpper & " suggested the article '" & lbltitle.Text & "' " & TextBox1.Text
                mail.Body = lbltitle.Text & "' br / '" & lbldescription.Text
                SmtpServer.Send(mail)
                lblEmail.Visible = True
                lblEmail.Text = "Email sent successfully to " & txtemail.Text
                lblemailerror.Visible = False
                txtemail.Text = ""
                TextBox1.Text = ""
                'MsgBox("mail send??")
            Catch ex As Exception
                MsgBox(ex.ToString)
                txtemail.Text = ""
            End Try
        End If

    End Sub

    Protected Sub FormView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewPageEventArgs)

    End Sub
End Class